﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Console = System.Diagnostics.Debug;
using System.Diagnostics;

namespace LinqToSQL.stuff
{
    static class Linq
    {
        //Clause
        //from
        //where
        //select
        //group
        //into
        //orderby
        //join
        //let
        //in
        //on
        //equals
        //by
        //ascending
        //descending

        static AdventureWorksAddressDataContext dc = new AdventureWorksAddressDataContext("Data Source=localhost;Initial Catalog=AdventureWorksLT;Integrated Security=True");

        #region Static helpers
        public static IEnumerable<string> ToUpper(this IEnumerable<string> source)
        {
            foreach (string s in source)
            {
                Console.WriteLine("Yield returning: {0}", s);
                yield return s.ToUpper();
            }
        }

        public static IEnumerable<string> ToUpperSlow(this IEnumerable<string> source)
        {
            List<string> result = new List<string>();
            foreach (string s in source)
            {
                Console.WriteLine("Adding {0} to list", s);
                result.Add(s);
            }
            return result;
        }
        #endregion

        static public void chainOfQueriesExample()
        {
            string[] sa = new[] {
                "#33",
                "#22",
                "% this is text",
                "#18"
            };

            var justNumbersAsStrings =
            from s in sa
            where s.StartsWith("#")
            select s.Substring(1);

            var numbersAsInts =
            from j in justNumbersAsStrings
            select Int32.Parse(j);

            var sum =
            numbersAsInts.Sum();

            Console.WriteLine(sum);
        }

        /// <summary>
        /// uses yield
        /// </summary>
        static public void DeferredExecutionExample()
        {
            string[] sa = new[] {"aaa",
                "Bbb",
                "CCc"
            };

            //fast mode
            Console.WriteLine("Before using ToUpper()");
            var sb = sa.ToUpper();

            Console.WriteLine("After using ToUpper()");
            Console.WriteLine("Before iterating the collection in sb");

            foreach (string s in sb)
                Console.WriteLine("Within iteration, s: {0}", s);

            //slow mode
            Console.WriteLine("Before using ToUpper()");
            sb = sa.ToUpperSlow();

            Console.WriteLine("After using ToUpper()");
            Console.WriteLine("Before iterating the collection in sb");

            foreach (string s in sb)
                Console.WriteLine("Within iteration, s: {0}", s);
        }

        static public void AggregationTest()
        {
            int[] ia = { 1, 5, 2, 6, 7 };
            Console.WriteLine(ia.Sum());

            Console.WriteLine(ia.Aggregate((total, i) => total += i));
            int initialTotal = 0;
            Console.WriteLine(ia.Aggregate(initialTotal, (total, i) => total += i));

            string[] sia = { "aaa", "bbb", "ccc" };
            Console.WriteLine(sia.Aggregate((a, i) => a += i));
        }

        static public void GroupTest()
        {
            var people = new[] {
                new { Name = "Bob", Age = 3 },
                new { Name = "Bill", Age = 3 },
                new { Name = "Mary", Age = 4 },
                new { Name = "June", Age = 3 },
                new { Name = "Nancy", Age = 4 },
                new { Name = "Shelly", Age = 4 },
                new { Name = "Cheryl", Age = 3 },
                new { Name = "Joe", Age = 3 }
            };

            //var groupedByAge = people.GroupBy(s => s.Age);
            var groupedByAge = from p in people
                               group p by p.Age;

            foreach (var group in groupedByAge)
            {
                // group implements IGrouping
                Console.WriteLine(string.Format("Group of people aged {0}", group.Key));
                foreach (var person in group)
                {
                    // person is an instance of the above anonymous type
                    Console.WriteLine(person.Name);
                }
                Console.WriteLine("");
            }
        }

        static public class DetailedJoinExample
        {
            // Simple customer class
            public class Customer
            {
                public string FirstName { get; set; }
                public string LastName { get; set; }
                public string EmailAddress { get; set; }
                // Overrides the Object.ToString( ) to provide a
                // string representation of the object properties.
                public override string ToString()
                {
                    return string.Format("{0} {1}\nEmail: {2}",
                    FirstName, LastName, EmailAddress);
                }
            }


            // Customer address class
            public class Address
            {
                public string Name { get; set; }
                public string Street { get; set; }
                public string City { get; set; }
                // Overrides the Object.ToString( ) to provide a
                // string representation of the object properties.
                public override string ToString()
                {
                    return string.Format("{0}, {1}", Street, City);
                }
            }

            // Main program
            public class Tester
            {
                static public void run()
                {
                    List<Customer> customers = CreateCustomerList();
                    List<Address> addresses = CreateAddressList();
                    var result2 =
                        from customer in customers
                        join address in addresses
                            on string.Format("{0} {1}", customer.FirstName, customer.LastName) equals address.Name
                        orderby customer.LastName, address.Street descending
                        select new { Customer = customer, Address = address.Street };

                    var result = customers.Join(addresses,
                            customer => string.Format("{0} {1}", customer.FirstName, customer.LastName),
                            address => address.Name,
                            (customer, address) => new
                            {
                                Customer = customer,
                                Address = address
                            })
                        .OrderBy(ca => ca.Customer.LastName)
                        .ThenByDescending(ca => ca.Address.Street);
                    foreach (var ca in result)
                    {
                        Console.WriteLine(string.Format("{0}\nAddress: {1}",
                        ca.Customer, ca.Address));
                    }
                    foreach (var ca in result2)
                    {
                        Console.WriteLine(string.Format("{0}\nAddress: {1}",
                        ca.Customer, ca.Address));
                    }
                }

                // Create a customer list with sample data
                private static List<Customer> CreateCustomerList()
                {
                    List<Customer> customers = new List<Customer>
                    {
                    new Customer { FirstName = "Orlando",
                    LastName = "Gee",
                    EmailAddress = "orlando0@adventure-works.com"},
                    new Customer { FirstName = "Keith",
                    LastName = "Harris",
                    EmailAddress = "keith0@adventure-works.com" },
                    new Customer { FirstName = "Donna",
                    LastName = "Carreras",
                    EmailAddress = "donna0@adventure-works.com" },
                    new Customer { FirstName = "Janet",
                    LastName = "Gates",
                    EmailAddress = "janet1@adventure-works.com" },
                    new Customer { FirstName = "Lucy",
                    LastName = "Harrington",
                    EmailAddress = "lucy0@adventure-works.com" }
                    };
                    return customers;
                }

                private static List<Address> CreateAddressList()
                {
                    List<Address> addresses = new List<Address>
                {
                new Address { Name = "Janet Gates",
                Street = "165 North Main",
                City = "Austin" },
                new Address { Name = "Keith Harris",
                Street = "3207 S Grady Way",
                City = "Renton" },
                new Address { Name = "Janet Gates",
                Street = "800 Interchange Blvd.",
                City = "Austin" },
                new Address { Name = "Keith Harris",
                Street = "7943 Walnut Ave",
                City = "Renton" },
                new Address { Name = "Orlando Gee",
                Street = "2251 Elliot Avenue",
                City = "Seattle" }
                };
                    return addresses;
                }
            }
        }

        static public void SimpleTests()
        {
            int[] scores = { 90, 71, 82, 93, 75, 82 };

            var query = from s in scores
                        where s > 80
                        orderby s descending
                        select s;
            IEnumerable<int> sameQuery = query;

            foreach (int score in query)
                Debug.WriteLine(score);

            //grouping
            var similarScores =
                (from s in scores
                 orderby s descending
                 group s by s);

            var similarScoresOrdered =
                (from g in similarScores
                 let counted = g.Count()
                 orderby counted descending
                 select new { count = counted, g.Key }).ToList();

            //grouping with countinuations
            var similarScoresOrdered2 =
                (from s in scores
                 orderby s descending
                 group s by s into scoresGrouped
                 let counted = scoresGrouped.Count()
                 orderby counted descending
                 select new { count = counted, scoresGrouped.Key }).ToList();

            //selecting with countinuations
            var largeEvenNumbers =
                (from s in scores
                 where s > 80
                 select s into tmp
                 where tmp % 2 == 0
                 select tmp).ToList();

            //join
            var customerData =
                (from c in dc.Customers
                 join cd in dc.CustomerAddresses on c.CustomerID equals cd.CustomerID
                 join d in dc.Addresses on cd.AddressID equals d.AddressID
                 select new { c, d.CustomerAddresses }).Distinct().ToList();

            //subquery example with lambda
            var AveragePriceByProductCategory =
                (from p in dc.Products
                 group p by p.ProductCategory.Name into catGroup
                 select new
                 {
                     category = catGroup.Key,
                     AveragePrice =
                         (from c in catGroup
                          select c.ListPrice).Average()
                 }).OrderBy(x => x.category).ToList();

            Func<Product, bool> comparissonOption = delegate(Product p)
            {
                return p.Weight > 10;
            };
            var heavyProducts = dc.Products.Where(comparissonOption).ToList();
        }

        static public class AditionalGroupExample
        {
            #region data
            public enum GradeLevel { FirstYear = 1, SecondYear, ThirdYear, FourthYear };
            public class Student
            {
                public string FirstName { get; set; }
                public string LastName { get; set; }
                public int ID { get; set; }
                public GradeLevel Year;
                public List<int> ExamScores;
            }

            public static List<Student> students = new List<Student>
                    {
                        new Student {FirstName = "Terry", LastName = "Adams", ID = 120, Year = GradeLevel.SecondYear, ExamScores = new List<int>{ 99, 82, 81, 79}},
                        new Student {FirstName = "Fadi", LastName = "Fakhouri", ID = 116, Year = GradeLevel.ThirdYear,ExamScores = new List<int>{ 99, 86, 90, 94}},
                        new Student {FirstName = "Hanying", LastName = "Feng", ID = 117, Year = GradeLevel.FirstYear, ExamScores = new List<int>{ 93, 92, 80, 87}},
                        new Student {FirstName = "Cesar", LastName = "Garcia", ID = 114, Year = GradeLevel.FourthYear,ExamScores = new List<int>{ 97, 89, 85, 82}},
                        new Student {FirstName = "Debra", LastName = "Garcia", ID = 115, Year = GradeLevel.ThirdYear, ExamScores = new List<int>{ 35, 72, 91, 70}},
                        new Student {FirstName = "Hugo", LastName = "Garcia", ID = 118, Year = GradeLevel.SecondYear, ExamScores = new List<int>{ 92, 90, 83, 78}},
                        new Student {FirstName = "Sven", LastName = "Mortensen", ID = 113, Year = GradeLevel.FirstYear, ExamScores = new List<int>{ 88, 94, 65, 91}},
                        new Student {FirstName = "Claire", LastName = "O'Donnell", ID = 112, Year = GradeLevel.FourthYear, ExamScores = new List<int>{ 75, 84, 91, 39}},
                        new Student {FirstName = "Svetlana", LastName = "Omelchenko", ID = 111, Year = GradeLevel.SecondYear, ExamScores = new List<int>{ 97, 92, 81, 60}},
                        new Student {FirstName = "Lance", LastName = "Tucker", ID = 119, Year = GradeLevel.ThirdYear, ExamScores = new List<int>{ 68, 79, 88, 92}},
                        new Student {FirstName = "Michael", LastName = "Tucker", ID = 122, Year = GradeLevel.FirstYear, ExamScores = new List<int>{ 94, 92, 91, 91}},
                        new Student {FirstName = "Eugene", LastName = "Zabokritski", ID = 121, Year = GradeLevel.FourthYear, ExamScores = new List<int>{ 96, 85, 91, 60}}
                    };
            #endregion

            //Helper method
            public static int GetPercentile(Student s)
            {
                double avg = s.ExamScores.Average();
                return avg > 0 ? (int)avg / 10 : 0;
            }



            public static void QueryHighScores(int exam, int score)
            {
                var highScores = from student in students
                                 where student.ExamScores[exam] > score
                                 select new { Name = student.FirstName, Score = student.ExamScores[exam] };

                foreach (var item in highScores)
                {
                    Console.WriteLine(string.Format("{0,-15}{1}", item.Name, item.Score));
                }
            }

            public static void GroupNewType_ByRange()
            {
                Console.WriteLine("\r\nGroup by numeric range and project into a new anonymous type:");

                var queryNumericRange =
                    from student in students
                    let percentile = GetPercentile(student)
                    group new { student.FirstName, student.LastName } by percentile into percentGroup
                    orderby percentGroup.Key
                    select percentGroup;

                // Nested foreach required to iterate over groups and group items.
                foreach (var studentGroup in queryNumericRange)
                {
                    Console.WriteLine(string.Format("Key: {0}", (studentGroup.Key * 10)));
                    foreach (var item in studentGroup)
                    {
                        Console.WriteLine(string.Format("\t{0}, {1}", item.LastName, item.FirstName));
                    }
                }
            }

            public static void GroupByCompositeKey()
            {

                var queryHighScoreGroups =
                    from student in students
                    group student by new { FirstLetter = student.LastName[0], Score = student.ExamScores[0] > 85 } into studentGroup
                    orderby studentGroup.Key.FirstLetter
                    select studentGroup;

                Console.WriteLine("\r\nGroup and order by a compound key:");
                foreach (var scoreGroup in queryHighScoreGroups)
                {
                    string s = scoreGroup.Key.Score == true ? "more than" : "less than";
                    Console.WriteLine(string.Format("Name starts with {0} who scored {1} 85", scoreGroup.Key.FirstLetter, s));
                    foreach (var item in scoreGroup)
                    {
                        Console.WriteLine(string.Format("\t{0} {1}", item.FirstName, item.LastName));
                    }
                }
            }
        }

        static public void AvoidNull()
        {
            var query =
                from p in dc.Products
                where p.ProductID != null
                join pm in dc.ProductModels
                    on p.ProductID equals pm.ProductModelID
                select new { p, pm };
        }

        public class CustomJoins
        {

            #region Constructor
            private static CustomJoins _Instance = null;
            private CustomJoins()
            {
            }
            static readonly private object padLock = new object();
            public static CustomJoins Instance
            {
                get
                {
                    if (_Instance == null)
                        lock (padLock)
                        {
                            if (_Instance == null)
                                _Instance = new CustomJoins();
                        }
                    return _Instance;
                }
            }
            #endregion

            #region Data

            class Person
            {
                public string FirstName { get; set; }
                public string LastName { get; set; }
            }

            class Pet
            {
                public string Name { get; set; }
                public Person Owner { get; set; }
            }

            class Product
            {
                public string Name { get; set; }
                public int CategoryID { get; set; }
            }

            class Category
            {
                public string Name { get; set; }
                public int ID { get; set; }
            }

            // Specify the first data source.
            List<Category> categories = new List<Category>()
                 { 
                     new Category(){Name="Beverages", ID=001},
                     new Category(){ Name="Condiments", ID=002},
                     new Category(){ Name="Vegetables", ID=003},         
                 };

            // Specify the second data source.
            List<Product> products = new List<Product>()
                {
                   new Product{Name="Tea",  CategoryID=001},
                   new Product{Name="Mustard", CategoryID=002},
                   new Product{Name="Pickles", CategoryID=002},
                   new Product{Name="Carrots", CategoryID=003},
                   new Product{Name="Bok Choy", CategoryID=003},
                   new Product{Name="Peaches", CategoryID=005},
                   new Product{Name="Melons", CategoryID=005},
                   new Product{Name="Ice Cream", CategoryID=007},
                   new Product{Name="Mackerel", CategoryID=012},
                 };
            #endregion

            public void CrossJoin()
            {
                var crossJoinQuery =
                    from c in categories
                    from p in products
                    select new { c.ID, p.Name };

                Console.WriteLine("Cross Join Query:");
            }

            public void NonEquijoin()
            {
                var nonEquijoinQuery =
                    from p in products
                    let catIds = from c in categories
                                 select c.ID
                    where catIds.Contains(p.CategoryID)
                    select new { Product = p.Name, CategoryID = p.CategoryID };

                Console.WriteLine("Non-equijoin query:");
            }

            public void CompositeKey()
            {
                AdventureWorksAddressDataContext db = dc;
                var query = from c in db.Customers
                            from a in db.Addresses
                            join ca in db.CustomerAddresses
                                on new { cid = c.CustomerID, a.AddressID } equals new
                                {
                                    cid = ca.CustomerID, //the name is no required if both match
                                    ca.AddressID
                                } into details
                            from d in details
                            select new { c, a };
                var result = query.ToList();
            }

            /// <summary>
            /// A group join produces a hierarchical result sequence, which associates elements in the left source sequence with one or more matching elements in the right side source sequence. A group join has no equivalent in relational terms; it is essentially a sequence of object arrays.
            ///If no elements from the right source sequence are found to match an element in the left source, the join clause will produce an empty array for that item. Therefore, the group join is still basically an inner-equijoin except that the result sequence is organized into groups.
            ///If you just select the results of a group join, you can access the items, but you cannot identify the key that they match on. Therefore, it is generally more useful to select the results of the group join into a new type that also has the key name, as shown in the previous example.
            /// </summary>
            public void GroupJoinExample()
            {
                Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" };
                Person terry = new Person { FirstName = "Terry", LastName = "Adams" };
                Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" };
                Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" };

                Pet barley = new Pet { Name = "Barley", Owner = terry };
                Pet boots = new Pet { Name = "Boots", Owner = terry };
                Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte };
                Pet bluemoon = new Pet { Name = "Blue Moon", Owner = terry };
                Pet daisy = new Pet { Name = "Daisy", Owner = magnus };

                // Create two lists.
                List<Person> people = new List<Person> { magnus, terry, charlotte, arlene };
                List<Pet> pets = new List<Pet> { barley, boots, whiskers, bluemoon, daisy };

                // Create a list where each element is an anonymous type
                // that contains the person's first name and a collection of 
                // pets that are owned by them.
                var allPeopleQ = (from p in people
                                  join pet in pets on p equals pet.Owner into hisPets
                                  select new { p.FirstName, p.LastName, pets = hisPets.ToArray() });
                var justWithPetsQ = from p in allPeopleQ
                                    where p.pets.Count() > 0
                                    select p;
                var all = allPeopleQ.ToArray();
                var withPets = justWithPetsQ.ToArray();
            }

            public static void LeftOuterJoinExample()
            {
                Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" };
                Person terry = new Person { FirstName = "Terry", LastName = "Adams" };
                Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" };
                Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" };

                Pet barley = new Pet { Name = "Barley", Owner = terry };
                Pet boots = new Pet { Name = "Boots", Owner = terry };
                Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte };
                Pet bluemoon = new Pet { Name = "Blue Moon", Owner = terry };
                Pet daisy = new Pet { Name = "Daisy", Owner = magnus };

                // Create two lists.
                List<Person> people = new List<Person> { magnus, terry, charlotte, arlene };
                List<Pet> pets = new List<Pet> { barley, boots, whiskers, bluemoon, daisy };

                //var query = from person in people
                //            join pet in pets on person equals pet.Owner into gj
                //            from subpet in gj.DefaultIfEmpty()
                //            select new { person.FirstName, PetName = (subpet == null ? String.Empty : subpet.Name) };
                var query = from person in people
                            join pet in pets on person equals pet.Owner into gj
                            //from subpet in gj.DefaultIfEmpty()
                            select new { person.FirstName, pets = gj.ToList() };
                var result = query.ToList();

                var other = (from person in people
                             join pet in pets on person equals pet.Owner
                             select new { person, pet }).ToList();
            }
        }

        public class IteratingInnerLists
        {
            public class Student
            {
                public string LastName { get; set; }
                public List<int> Scores { get; set; }
            }

            static public void run()
            {
                // Use a collection initializer to create the data source. Note that 
                // each element in the list contains an inner sequence of scores.
                List<Student> students = new List<Student>
                {
                   new Student {LastName="Omelchenko", Scores= new List<int> {97, 72, 81, 60}},
                   new Student {LastName="O'Donnell", Scores= new List<int> {75, 84, 91, 39}},
                   new Student {LastName="Mortensen", Scores= new List<int> {88, 94, 65, 85}},
                   new Student {LastName="Garcia", Scores= new List<int> {97, 89, 85, 82}},
                   new Student {LastName="Beebe", Scores= new List<int> {35, 72, 91, 70}} 
                };

                // Use a compound from to access the inner sequence within each element.
                // Note the similarity to a nested foreach statement.
                var scoreQuery = from student in students
                                 from score in student.Scores
                                 where score > 80
                                 orderby score
                                 select new { Last = student.LastName, score };

                // Execute the queries.
                Console.WriteLine("scoreQuery:");
                foreach (var student in scoreQuery)
                {
                    Console.WriteLine(string.Format("{0} Score: {1}", student.Last, student.score));
                }

                // Keep the console window open in debug mode.
                Console.WriteLine("Press any key to exit.");
            }
        }

        public class IntoExamples
        {
            public static void test()
            {
                Random rnd = new Random();
                List<int> numbers = Enumerable.Range(0, 10).Select(x => rnd.Next() % 100).ToList();
                //anonimous array
                var classes = new []{ 
                    new { type = "odd", result = 0 }, 
                    new { type  = "even", result = 1 }, 
                    new { type = "unreal", result = 2}
                };

                //into for grouping and into for select
                var q1 = from i in numbers
                         let isEven = i % 2 == 0
                         group i by isEven into grp
                         select new { type = grp.Key ? "even" : "odd", max = grp.ToList().Max() } into result
                         orderby result.max descending
                         select result;
                var q1r = q1.ToList();

                //into in join
                var q2 = from item in classes
                         join n in numbers on item.result equals n % 2 into myNums
                         select new { item.type, nums = myNums.ToList() };
                var q2r = q2.ToList();

            }
        }
    }
}
